package etc.controller;

import com.alibaba.fastjson.JSON;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/hive")
public class IndexController {
    @Autowired
    @Qualifier("jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    //车辆总数统计
    @GetMapping("/countall")
    public String list() {
        String sql = "SELECT COUNT(*) AS TotalCarCount FROM insz";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    //车型统计
    @GetMapping("/countcx")
    public String cx() {
        String sql = "SELECT CX, COUNT(*) AS CarTypeCount FROM insz GROUP BY CX order by CarTypeCount desc";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    //车型统计（合并）
    @GetMapping("/updown3")
    public String updown3() {
        String sql = "SELECT " +
                "CASE WHEN CX IN ('一型车(客)', '一型车(货)', '二型车(客)', '二型车(货)', '三型车(客)', '三型车(货)') THEN 'down3' ELSE 'up3' END AS VehicleLevel, " +
                "COUNT(*) AS VehicleCount " +
                "FROM insz " +
                "GROUP BY CASE WHEN CX IN ('一型车(客)', '一型车(货)', '二型车(客)', '二型车(货)', '三型车(客)', '三型车(货)') THEN 'down3' ELSE 'up3' END";

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    //入站站点排行（暂时为全部显示，后面按照需求阉割）
    @GetMapping("/inrank")
    public String incountrank() {
        String sql = "SELECT " +
                "SFZRKMC AS Station, " +
                "SUM(CASE WHEN BZ = '深圳入' THEN 1 ELSE 0 END) AS InCount " +
                "FROM insz " +
                "GROUP BY SFZRKMC " +
                "ORDER BY InCount DESC";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    //出站站点排行（暂时为全部显示，后面按照需求阉割）
    @GetMapping("/outrank")
    public String outcountrank() {
        String sql = "SELECT " +
                "SFZRKMC AS Station, " +
                "SUM(CASE WHEN BZ = '深圳出' THEN 1 ELSE 0 END) AS OutCount " +
                "FROM outsz " +
                "GROUP BY SFZCKMC " +
                "ORDER BY InCount DESC";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    //入深来源
    @GetMapping("/source")
    public String inSzSource() {
        String sql = "SELECT" +
                "                        SUBSTRING(SFZRKMC, 1, 2) AS StationPrefix," +
                "                        COUNT(*) AS VehicleCount" +
                "                        FROM insz" +
                "                        WHERE BZ = '深圳入'" +
                "                        GROUP BY SUBSTRING(SFZRKMC, 1, 2)" +
                "                        ORDER BY VehicleCount DESC";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }
}

